package com.os.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.os.dao.SelectSuqiuDaoS;
import com.os.model.DeskS;
import com.os.model.FoodS;
import com.os.model.UserM;
import com.os.util.JdbcConUtilM;

public class SelectSuqiuDaoImplS implements SelectSuqiuDaoS {

	@Override
	public List<DeskS> selectDidS() {
		List<DeskS> dnum = new ArrayList<DeskS>();
		StringBuffer sql = new StringBuffer();
		sql.append("select DISTINCT did from o_desk");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			ResultSet re = pst.executeQuery();
			while(re.next()){
				DeskS d = new DeskS();
				d.setDid(re.getInt("did"));
				dnum.add(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return dnum;
	}

	@Override
	public List<DeskS> selectOidS() {
		List<DeskS> dnum = new ArrayList<DeskS>();
		StringBuffer sql = new StringBuffer();
		sql.append("select DISTINCT oid from o_desk");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			ResultSet re = pst.executeQuery();
			while(re.next()){
				DeskS d = new DeskS();
				d.setOid(re.getInt("oid"));
				dnum.add(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return dnum;
	}

	@Override
	public List<FoodS> selectFnameS() {
		List<FoodS> dnum = new ArrayList<FoodS>();
		StringBuffer sql = new StringBuffer();
		sql.append("select DISTINCT fname from o_food");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			ResultSet re = pst.executeQuery();
			while(re.next()){
				FoodS d = new FoodS();
				d.setFname(re.getString("fname"));
				dnum.add(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return dnum;
	}

	@Override
	public List<UserM> selectDnameS() {
		List<UserM> dnum = new ArrayList<UserM>();
		StringBuffer sql = new StringBuffer();
		sql.append("select DISTINCT uname from o_user where rid=1");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			ResultSet re = pst.executeQuery();
			while(re.next()){
				UserM d = new UserM();
				d.setUname(re.getString("uname"));
				dnum.add(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return dnum;
	}

	@Override
	public List<UserM> selectJnameS() {
		List<UserM> dnum = new ArrayList<UserM>();
		StringBuffer sql = new StringBuffer();
		sql.append("select DISTINCT uname from o_user where rid=3");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			ResultSet re = pst.executeQuery();
			while(re.next()){
				UserM d = new UserM();
				d.setUname(re.getString("uname"));
				dnum.add(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return dnum;
	}

	@Override
	public int selectFidByFnameS(String fname) {
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("select DISTINCT fid from o_food where fname=?");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, fname);
			ResultSet re = pst.executeQuery();
			if(re.next()){
				flag = re.getInt("fid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return flag;
	}

	@Override
	public int selectDidByDnameS(String dname) {
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("select DISTINCT uid from o_user where uname=?");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, dname);
			ResultSet re = pst.executeQuery();
			if(re.next()){
				flag = re.getInt("uid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return flag;
	}

	@Override
	public int selectJidByJnameS(String jname) {
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("select DISTINCT uid from o_user where uname=?");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, jname);
			ResultSet re = pst.executeQuery();
			if(re.next()){
				flag = re.getInt("uid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return flag;
	}

	@Override
	public boolean addSuqiuS(int did, int oid, int fid, int rcontent, int r_rid, int uid) {
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO o_request ");
		sql.append(" (did,oid,fid,rcontent,rcount,rstate,r_rid,uid)  ");
		sql.append(" VALUES (?,?,?,?,1,-1,?,?)");
		Connection con = JdbcConUtilM.getCon();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			pst.setInt(2, oid);
			pst.setInt(3, fid);
			pst.setInt(4, rcontent);
			pst.setInt(5, r_rid);
			pst.setInt(6, uid);
			int i = pst.executeUpdate();
			if(i>0){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return flag;
	}

}
